from flask import Flask,jsonify
from flask_sqlalchemy import SQLAlchemy
import pymysql
import psycopg2

pymysql.install_as_MySQLdb()

app=Flask(__name__)

# 配置数据库的地址
# mysql://root:123456@127.0.0.1/flask_sql_demo  123456:mysql的密码， 127.0.0.1:ip flask_sql_demo:数据库名称
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://postgres:123456789@localhost:5432/changshapop'

# 跟踪数据库的修改 --> 不建议开启 未来的版本中会移除
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# 生成数据库访问对象
db = SQLAlchemy(app)
db.init_app(app)
# 创建User表
class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer,autoincrement=True,primary_key=True)
    name = db.Column(db.String(10))
    activate = db.Boolean(db.Boolean)
    heigh = db.Column(db.Float)

    def save(self):
        db.session.add(self)
        db.session.commit()
    def __str__(self):
        return self.name

class Phone(db.Model):
    __tablename__ = 'phone'
    id = db.Column(db.Integer,autoincrement=True,primary_key=True)
    content = db.Column(db.String(11))
    user_id = db.Column(db.Integer,db.ForeignKey('user.id',ondelete='CASCADE'))


    def save(self):
        db.session.add(self)
        db.session.commit()

    def __str__(self):
        return self.content

# 获取数据库表
@app.route('/')
def db_test():
    dict_show = {}  # 定义字典用于数据展示
    # 反射数据库中已存在的表，并获取所有存在的表对象。
    db.reflect()
    # 获取所有表名
    all_table = {table_obj.name: table_obj for table_obj in db.get_tables_for_bind()}
    print(all_table)
    # 获取demo表的所有数据
    all_data = db.session.query(all_table['fiveCommunitypop'])
    for data in all_data:
        print(data)  #查看数据
        dict_show[data[0]] = [data[1], data[2]]
    # 将字典转换成json数据
    return jsonify(dict_show)

if __name__=="__main__":
     app.run(debug=True)
     # db.drop_all()
     # db.create_all()
    # user1 = User(name='小明',activate=True,heigh=1.5)
    # user1.save()
    # phone = Phone(content='12345678901',user_id=user1.id)
    # phone.save()
    # print(user1,phone)
